﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for DAO
/// </summary>
public class DAO : IDao
{
    private static DAO daoInstance;
    private SqlConnection conn;
    private string source = string.Format("Data Source={0}\\SQLEXPRESS;initial catalog={1};user id={2};password={3}",
                                           Environment.MachineName,
                                           "EWmainServerDB",
                                           "AppAdminMain",
                                           "XxX01012010"
                                            );

    #region Singleton 

    private DAO()
    {
        conn = new SqlConnection(source);
    }


    public static DAO getInstance()
    {
        if (daoInstance == null)
        {
            daoInstance = new DAO();
        }
        return daoInstance;
    }

    #endregion

    #region Methods to open/close database connection

    private void openConn()
    {
        conn.Open();
    }
   
  

    private void closeConn()
    {
        conn.Close();
    }

    #endregion

    //Method to tjeck if license is valid
    public bool isLicenseValid(string customerId)
    {
        bool succeed = false;
        try
        {
            string q = "exec procIsLicenseValid @customerID";
            SqlCommand cmd = new SqlCommand(q, conn);
            cmd.Parameters.Add("@customerID", SqlDbType.VarChar).Value = customerId;
            openConn();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                succeed = reader.GetBoolean(0);
            }
            return succeed;
        }
        catch (System.Exception)
        {
            return succeed;
        }
        finally
        {
            closeConn();
        }


    }

    //Method to submit log entries to database
    public bool submitLogEntries(List<LogEntry> logEntries)
    {
        bool succeed = false;
        openConn();
        SqlTransaction tx = conn.BeginTransaction();
        try
        {      
            foreach (LogEntry entry in logEntries)
            {
                bool person = entry.Gender != null;
                    string q = "insertLogEntry";
                    SqlCommand cmd = new SqlCommand(q, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@customerID", SqlDbType.VarChar).Value = entry.CustomerID;
                    cmd.Parameters.Add("@cpr", SqlDbType.VarChar).Value = entry.Cpr;
                    cmd.Parameters.Add("@ts", SqlDbType.DateTime).Value = entry.TimeStamp;
                    cmd.Parameters.Add("@eventdsc", SqlDbType.VarChar).Value = entry.EventDsc;
                    cmd.Parameters.Add("@shift", SqlDbType.DateTime).Value = entry.Shift;
                    if (person)
                    {
                        if (entry.Gender == true)
                            cmd.Parameters.Add("@gender", SqlDbType.Char).Value = "M";
                        else
                            cmd.Parameters.Add("@gender", SqlDbType.Char).Value = "F";
                    }
                    else
                    {
                        cmd.Parameters.Add("@gender", SqlDbType.Char).Value = "U";
                    }
                    if (person)
                    {
                        cmd.Parameters.Add("@zipcode", SqlDbType.VarChar).Value = entry.ZipCode;
                        cmd.Parameters.Add("@birthdate", SqlDbType.VarChar).Value = entry.Birthdate;
                    }
                    cmd.Transaction = tx;
                    cmd.ExecuteNonQuery();
            }
            tx.Commit();
            succeed = true;
            return succeed;
        }
        catch (System.Exception)
        {

            if (tx != null) tx.Rollback();
            return succeed;

        }
        finally
        {
            tx.Dispose();    
            closeConn();  
            
        }

    }

}